<?php

namespace App\Model;


use Illuminate\Support\Facades\DB;

class GiftPearlStream extends BaseModel
{
    protected $table = 'gift_pearl_stream';
    public $timestamps = false;
    protected $primaryKey = 'stream_id';

    public function statisticsGiftStream($input = []){
        $limit = $input['limit'] ?? 10;
        $page = $input['page'] ?? 0;
        $default = $input['default'] ?? 1;
        $starttime = $input['start_date'] ?? 0;
        $endtime = $input['end_date'] ?? 0;
        $dm = $input['type'] ?? 'd';
        $having = '1=1';
        $format = '%Y-%m-%d';
        if($dm == 'd'){
            $format = '%Y-%m-%d';
        }else if($dm == 'm'){
            $format = '%Y-%m';
            if (!empty($starttime)){
                $starttime = substr($starttime, 0,7);
            }
            if(!empty($endtime)){
                $endtime = substr($endtime,0,7);
            }
        }

        if(!empty($starttime)){
            $having .= " AND '{$starttime}'<=FROM_UNIXTIME(change_timestamp,'{$format}')";
        }
        if(!empty($endtime)){
            $having .= " AND '{$endtime}'>=FROM_UNIXTIME(change_timestamp,'{$format}')";
        }
        $sql = 'SELECT FROM_UNIXTIME(change_timestamp,\''.$format.'\') as ctime,COUNT(*) as count, gift_id,gift_name,SUM(gift_num) as gift_sum,SUM(gift_num * gift_price) as price_sum FROM zb_gift_pearl_stream WHERE '.$having.' group BY gift_id,gift_name,FROM_UNIXTIME(change_timestamp,\''.$format.'\') ORDER BY ctime desc ,price_sum desc';

        $data = DB::connection('api')->select($sql);

        $totalStatistics = [];
        $totalStatistics['count'] = array_sum(array_column($data,'count'));
        $totalStatistics['gift_sum'] = array_sum(array_column($data,'gift_sum'));
        $totalStatistics['price_sum'] = array_sum(array_column($data,'price_sum'));
        $total = count($data);
        $list = collect($data)->forPage($page, $limit)->values()->toArray();
        return [ 'data' => $list, 'total' => $total, 'total_statistics' => $totalStatistics ,'default'=>$default];
    }

}
